{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Cookbook\n",
    "\n",
    "This notebook contains a miscellaneous collection of runnable examples illustrating various Splink techniques."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Array columns\n",
    "\n",
    "### Comparing array columns\n",
    "\n",
    "This example shows how we can use use `ArrayIntersectAtSizes` to assess the similarity of columns containing arrays."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "tags": [
     "hide_input"
    ]
   },
   "outputs": [],
   "source": [
    "# Uncomment and run this cell if you're running in Google Colab.\n",
    "# !pip install splink"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "tags": [
     "hide_input",
     "hide_output"
    ]
   },
   "outputs": [],
   "source": [
    "import logging\n",
    "logging.getLogger(\"splink\").setLevel(logging.ERROR)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>match_weight</th>\n",
       "      <th>match_probability</th>\n",
       "      <th>unique_id_l</th>\n",
       "      <th>unique_id_r</th>\n",
       "      <th>postcode_l</th>\n",
       "      <th>postcode_r</th>\n",
       "      <th>gamma_postcode</th>\n",
       "      <th>first_name_l</th>\n",
       "      <th>first_name_r</th>\n",
       "      <th>gamma_first_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-8.287568</td>\n",
       "      <td>0.003190</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>[A, B]</td>\n",
       "      <td>[C]</td>\n",
       "      <td>0</td>\n",
       "      <td>John</td>\n",
       "      <td>John</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-0.287568</td>\n",
       "      <td>0.450333</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>[A]</td>\n",
       "      <td>[A, B]</td>\n",
       "      <td>1</td>\n",
       "      <td>John</td>\n",
       "      <td>John</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-8.287568</td>\n",
       "      <td>0.003190</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>[A]</td>\n",
       "      <td>[C]</td>\n",
       "      <td>0</td>\n",
       "      <td>John</td>\n",
       "      <td>John</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-8.287568</td>\n",
       "      <td>0.003190</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>[B]</td>\n",
       "      <td>[A]</td>\n",
       "      <td>0</td>\n",
       "      <td>John</td>\n",
       "      <td>John</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-0.287568</td>\n",
       "      <td>0.450333</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>[B]</td>\n",
       "      <td>[A, B]</td>\n",
       "      <td>1</td>\n",
       "      <td>John</td>\n",
       "      <td>John</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>-8.287568</td>\n",
       "      <td>0.003190</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>[B]</td>\n",
       "      <td>[C]</td>\n",
       "      <td>0</td>\n",
       "      <td>John</td>\n",
       "      <td>John</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>-0.287568</td>\n",
       "      <td>0.450333</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>[A, B]</td>\n",
       "      <td>[B]</td>\n",
       "      <td>1</td>\n",
       "      <td>John</td>\n",
       "      <td>John</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>-0.287568</td>\n",
       "      <td>0.450333</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>[A, B]</td>\n",
       "      <td>[A]</td>\n",
       "      <td>1</td>\n",
       "      <td>John</td>\n",
       "      <td>John</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>6.712432</td>\n",
       "      <td>0.990554</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>[A, B]</td>\n",
       "      <td>[A, B]</td>\n",
       "      <td>2</td>\n",
       "      <td>John</td>\n",
       "      <td>John</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>-8.287568</td>\n",
       "      <td>0.003190</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>[A, B]</td>\n",
       "      <td>[C]</td>\n",
       "      <td>0</td>\n",
       "      <td>John</td>\n",
       "      <td>John</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   match_weight  match_probability  unique_id_l  unique_id_r postcode_l  \\\n",
       "0     -8.287568           0.003190            4            5     [A, B]   \n",
       "1     -0.287568           0.450333            3            4        [A]   \n",
       "2     -8.287568           0.003190            3            5        [A]   \n",
       "3     -8.287568           0.003190            2            3        [B]   \n",
       "4     -0.287568           0.450333            2            4        [B]   \n",
       "5     -8.287568           0.003190            2            5        [B]   \n",
       "6     -0.287568           0.450333            1            2     [A, B]   \n",
       "7     -0.287568           0.450333            1            3     [A, B]   \n",
       "8      6.712432           0.990554            1            4     [A, B]   \n",
       "9     -8.287568           0.003190            1            5     [A, B]   \n",
       "\n",
       "  postcode_r  gamma_postcode first_name_l first_name_r  gamma_first_name  \n",
       "0        [C]               0         John         John                 1  \n",
       "1     [A, B]               1         John         John                 1  \n",
       "2        [C]               0         John         John                 1  \n",
       "3        [A]               0         John         John                 1  \n",
       "4     [A, B]               1         John         John                 1  \n",
       "5        [C]               0         John         John                 1  \n",
       "6        [B]               1         John         John                 1  \n",
       "7        [A]               1         John         John                 1  \n",
       "8     [A, B]               2         John         John                 1  \n",
       "9        [C]               0         John         John                 1  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "import splink.comparison_library as cl\n",
    "from splink import DuckDBAPI, Linker, SettingsCreator, block_on\n",
    "\n",
    "\n",
    "data = [\n",
    "    {\"unique_id\": 1, \"first_name\": \"John\", \"postcode\": [\"A\", \"B\"]},\n",
    "    {\"unique_id\": 2, \"first_name\": \"John\", \"postcode\": [\"B\"]},\n",
    "    {\"unique_id\": 3, \"first_name\": \"John\", \"postcode\": [\"A\"]},\n",
    "    {\"unique_id\": 4, \"first_name\": \"John\", \"postcode\": [\"A\", \"B\"]},\n",
    "    {\"unique_id\": 5, \"first_name\": \"John\", \"postcode\": [\"C\"]},\n",
    "]\n",
    "\n",
    "df = pd.DataFrame(data)\n",
    "\n",
    "settings = SettingsCreator(\n",
    "    link_type=\"dedupe_only\",\n",
    "    blocking_rules_to_generate_predictions=[\n",
    "        block_on(\"first_name\"),\n",
    "    ],\n",
    "    comparisons=[\n",
    "        cl.ArrayIntersectAtSizes(\"postcode\", [2, 1]),\n",
    "        cl.ExactMatch(\"first_name\"),\n",
    "    ]\n",
    ")\n",
    "\n",
    "\n",
    "linker = Linker(df, settings, DuckDBAPI(), set_up_basic_logging=False)\n",
    "\n",
    "linker.inference.predict().as_pandas_dataframe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Blocking on array columns\n",
    "\n",
    "This example shows how we can use `block_on` to block on the individual elements of an array column - that is, pairwise comaprisons are created for pairs or records where any of the elements in the array columns match."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>match_weight</th>\n",
       "      <th>match_probability</th>\n",
       "      <th>unique_id_l</th>\n",
       "      <th>unique_id_r</th>\n",
       "      <th>postcode_l</th>\n",
       "      <th>postcode_r</th>\n",
       "      <th>gamma_postcode</th>\n",
       "      <th>first_name_l</th>\n",
       "      <th>first_name_r</th>\n",
       "      <th>gamma_first_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-0.287568</td>\n",
       "      <td>0.450333</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>[A, B]</td>\n",
       "      <td>[B]</td>\n",
       "      <td>1</td>\n",
       "      <td>John</td>\n",
       "      <td>John</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   match_weight  match_probability  unique_id_l  unique_id_r postcode_l  \\\n",
       "0     -0.287568           0.450333            1            2     [A, B]   \n",
       "\n",
       "  postcode_r  gamma_postcode first_name_l first_name_r  gamma_first_name  \n",
       "0        [B]               1         John         John                 1  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "import splink.comparison_library as cl\n",
    "from splink import DuckDBAPI, Linker, SettingsCreator, block_on\n",
    "\n",
    "\n",
    "data = [\n",
    "    {\"unique_id\": 1, \"first_name\": \"John\", \"postcode\": [\"A\", \"B\"]},\n",
    "    {\"unique_id\": 2, \"first_name\": \"John\", \"postcode\": [\"B\"]},\n",
    "    {\"unique_id\": 3, \"first_name\": \"John\", \"postcode\": [\"C\"]},\n",
    "\n",
    "]\n",
    "\n",
    "df = pd.DataFrame(data)\n",
    "\n",
    "settings = SettingsCreator(\n",
    "    link_type=\"dedupe_only\",\n",
    "    blocking_rules_to_generate_predictions=[\n",
    "        block_on(\"postcode\", arrays_to_explode=[\"postcode\"]),\n",
    "    ],\n",
    "    comparisons=[\n",
    "        cl.ArrayIntersectAtSizes(\"postcode\", [2, 1]),\n",
    "        cl.ExactMatch(\"first_name\"),\n",
    "    ]\n",
    ")\n",
    "\n",
    "\n",
    "linker = Linker(df, settings, DuckDBAPI(), set_up_basic_logging=False)\n",
    "\n",
    "linker.inference.predict().as_pandas_dataframe()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Other\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using DuckDB without pandas\n",
    "\n",
    "In this example, we read data directly using DuckDB and obtain results in native DuckDB `DuckDBPyRelation` format.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "┌─────────────────────┬──────────────────────┬─────────────┬───┬───────────────┬────────────┬────────────┬───────────┐\n",
       "│    match_weight     │  match_probability   │ unique_id_l │ … │ gamma_surname │   dob_l    │   dob_r    │ match_key │\n",
       "│       double        │        double        │    int64    │   │     int32     │  varchar   │  varchar   │  varchar  │\n",
       "├─────────────────────┼──────────────────────┼─────────────┼───┼───────────────┼────────────┼────────────┼───────────┤\n",
       "│  -11.83278901894715 │ 0.000274066864295451 │         758 │ … │             0 │ 2002-09-15 │ 2002-09-15 │ 0         │\n",
       "│ -10.247826518225994 │  0.0008217501639050… │         670 │ … │             0 │ 2006-12-05 │ 2006-12-05 │ 0         │\n",
       "│  -9.662864017504837 │  0.0012321189988629… │         558 │ … │             0 │ 2020-02-11 │ 2020-02-11 │ 0         │\n",
       "│  -9.470218939562441 │  0.0014078881864458… │         259 │ … │             1 │ 1983-03-07 │ 1983-03-07 │ 0         │\n",
       "│  -8.470218939562441 │ 0.002811817648042493 │         644 │ … │            -1 │ 1992-02-06 │ 1992-02-06 │ 0         │\n",
       "│  -8.287568102831404 │  0.0031901106569634… │         393 │ … │             3 │ 1991-05-06 │ 1991-04-12 │ 1         │\n",
       "│  -8.287568102831404 │  0.0031901106569634… │         282 │ … │             3 │ 2004-12-02 │ 2002-02-25 │ 1         │\n",
       "│  -8.287568102831404 │  0.0031901106569634… │         282 │ … │             3 │ 2004-12-02 │ 1993-03-01 │ 1         │\n",
       "│  -8.287568102831404 │  0.0031901106569634… │         531 │ … │             3 │ 1987-09-11 │ 2000-09-03 │ 1         │\n",
       "│  -8.287568102831404 │  0.0031901106569634… │         531 │ … │             3 │ 1987-09-11 │ 1990-10-06 │ 1         │\n",
       "│           ·         │            ·         │          ·  │ · │             · │     ·      │     ·      │ ·         │\n",
       "│           ·         │            ·         │          ·  │ · │             · │     ·      │     ·      │ ·         │\n",
       "│           ·         │            ·         │          ·  │ · │             · │     ·      │     ·      │ ·         │\n",
       "│   5.337135982495163 │   0.9758593366351407 │         554 │ … │             3 │ 2020-02-11 │ 2030-02-08 │ 1         │\n",
       "│   5.337135982495163 │   0.9758593366351407 │         774 │ … │             3 │ 2027-04-21 │ 2017-04-23 │ 1         │\n",
       "│   5.337135982495163 │   0.9758593366351407 │         874 │ … │             3 │ 2020-06-23 │ 2019-05-23 │ 1         │\n",
       "│   5.337135982495163 │   0.9758593366351407 │         409 │ … │             3 │ 2017-05-03 │ 2008-05-05 │ 1         │\n",
       "│   5.337135982495163 │   0.9758593366351407 │         415 │ … │             3 │ 2002-02-25 │ 1993-03-01 │ 1         │\n",
       "│   5.337135982495163 │   0.9758593366351407 │         740 │ … │             3 │ 2005-09-18 │ 2006-09-14 │ 1         │\n",
       "│   5.337135982495163 │   0.9758593366351407 │         417 │ … │             3 │ 2002-02-24 │ 1992-02-28 │ 1         │\n",
       "│   5.337135982495163 │   0.9758593366351407 │         534 │ … │             3 │ 1974-02-28 │ 1975-03-31 │ 1         │\n",
       "│   5.337135982495163 │   0.9758593366351407 │         286 │ … │             3 │ 1985-01-05 │ 1986-02-04 │ 1         │\n",
       "│   5.337135982495163 │   0.9758593366351407 │         172 │ … │             3 │ 2012-07-06 │ 2012-07-09 │ 1         │\n",
       "├─────────────────────┴──────────────────────┴─────────────┴───┴───────────────┴────────────┴────────────┴───────────┤\n",
       "│ 1800 rows (20 shown)                                                                          13 columns (7 shown) │\n",
       "└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import duckdb\n",
    "import tempfile\n",
    "import os\n",
    "\n",
    "import splink.comparison_library as cl\n",
    "from splink import DuckDBAPI, Linker, SettingsCreator, block_on, splink_datasets\n",
    "\n",
    "# Create a parquet file on disk to demontrate native DuckDB parquet reading\n",
    "df = splink_datasets.fake_1000\n",
    "temp_file = tempfile.NamedTemporaryFile(delete=True, suffix=\".parquet\")\n",
    "temp_file_path = temp_file.name\n",
    "df.to_parquet(temp_file_path)\n",
    "\n",
    "# Example would start here if you already had a parquet file\n",
    "duckdb_df = duckdb.read_parquet(temp_file_path)\n",
    "\n",
    "db_api = DuckDBAPI(\":default:\")\n",
    "settings = SettingsCreator(\n",
    "    link_type=\"dedupe_only\",\n",
    "    comparisons=[\n",
    "        cl.NameComparison(\"first_name\"),\n",
    "        cl.JaroAtThresholds(\"surname\"),\n",
    "    ],\n",
    "    blocking_rules_to_generate_predictions=[\n",
    "        block_on(\"first_name\", \"dob\"),\n",
    "        block_on(\"surname\"),\n",
    "    ],\n",
    ")\n",
    "\n",
    "linker = Linker(df, settings, db_api, set_up_basic_logging=False)\n",
    "\n",
    "result = linker.inference.predict().as_duckdbpyrelation()\n",
    "\n",
    "# Since result is a DuckDBPyRelation, we can use all the usual DuckDB API\n",
    "# functions on it.\n",
    "\n",
    "# For example, we can use the `sort` function to sort the results,\n",
    "# or could use result.to_parquet() to write to a parquet file.\n",
    "result.sort(\"match_weight\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Fixing `m` or `u` probabilities during training\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "<style>\n",
       "  #altair-viz-e9bf017f37784baeac18f1a32e54540e.vega-embed {\n",
       "    width: 100%;\n",
       "    display: flex;\n",
       "  }\n",
       "\n",
       "  #altair-viz-e9bf017f37784baeac18f1a32e54540e.vega-embed details,\n",
       "  #altair-viz-e9bf017f37784baeac18f1a32e54540e.vega-embed details summary {\n",
       "    position: relative;\n",
       "  }\n",
       "</style>\n",
       "<div id=\"altair-viz-e9bf017f37784baeac18f1a32e54540e\"></div>\n",
       "<script type=\"text/javascript\">\n",
       "  var VEGA_DEBUG = (typeof VEGA_DEBUG == \"undefined\") ? {} : VEGA_DEBUG;\n",
       "  (function(spec, embedOpt){\n",
       "    let outputDiv = document.currentScript.previousElementSibling;\n",
       "    if (outputDiv.id !== \"altair-viz-e9bf017f37784baeac18f1a32e54540e\") {\n",
       "      outputDiv = document.getElementById(\"altair-viz-e9bf017f37784baeac18f1a32e54540e\");\n",
       "    }\n",
       "    const paths = {\n",
       "      \"vega\": \"https://cdn.jsdelivr.net/npm/vega@5?noext\",\n",
       "      \"vega-lib\": \"https://cdn.jsdelivr.net/npm/vega-lib?noext\",\n",
       "      \"vega-lite\": \"https://cdn.jsdelivr.net/npm/vega-lite@5.17.0?noext\",\n",
       "      \"vega-embed\": \"https://cdn.jsdelivr.net/npm/vega-embed@6?noext\",\n",
       "    };\n",
       "\n",
       "    function maybeLoadScript(lib, version) {\n",
       "      var key = `${lib.replace(\"-\", \"\")}_version`;\n",
       "      return (VEGA_DEBUG[key] == version) ?\n",
       "        Promise.resolve(paths[lib]) :\n",
       "        new Promise(function(resolve, reject) {\n",
       "          var s = document.createElement('script');\n",
       "          document.getElementsByTagName(\"head\")[0].appendChild(s);\n",
       "          s.async = true;\n",
       "          s.onload = () => {\n",
       "            VEGA_DEBUG[key] = version;\n",
       "            return resolve(paths[lib]);\n",
       "          };\n",
       "          s.onerror = () => reject(`Error loading script: ${paths[lib]}`);\n",
       "          s.src = paths[lib];\n",
       "        });\n",
       "    }\n",
       "\n",
       "    function showError(err) {\n",
       "      outputDiv.innerHTML = `<div class=\"error\" style=\"color:red;\">${err}</div>`;\n",
       "      throw err;\n",
       "    }\n",
       "\n",
       "    function displayChart(vegaEmbed) {\n",
       "      vegaEmbed(outputDiv, spec, embedOpt)\n",
       "        .catch(err => showError(`Javascript Error: ${err.message}<br>This usually means there's a typo in your chart specification. See the javascript console for the full traceback.`));\n",
       "    }\n",
       "\n",
       "    if(typeof define === \"function\" && define.amd) {\n",
       "      requirejs.config({paths});\n",
       "      require([\"vega-embed\"], displayChart, err => showError(`Error loading script: ${err.message}`));\n",
       "    } else {\n",
       "      maybeLoadScript(\"vega\", \"5\")\n",
       "        .then(() => maybeLoadScript(\"vega-lite\", \"5.17.0\"))\n",
       "        .then(() => maybeLoadScript(\"vega-embed\", \"6\"))\n",
       "        .catch(showError)\n",
       "        .then(() => displayChart(vegaEmbed));\n",
       "    }\n",
       "  })({\"config\": {\"view\": {\"continuousWidth\": 300, \"continuousHeight\": 300, \"discreteHeight\": 300, \"discreteWidth\": 400}, \"header\": {\"title\": null}, \"title\": {\"anchor\": \"middle\", \"offset\": 10}}, \"hconcat\": [{\"mark\": \"bar\", \"encoding\": {\"color\": {\"value\": \"green\"}, \"row\": {\"field\": \"comparison_name\", \"header\": {\"labelAlign\": \"left\", \"labelAnchor\": \"middle\", \"labelAngle\": 0}, \"sort\": {\"field\": \"comparison_sort_order\"}, \"type\": \"nominal\"}, \"tooltip\": [{\"field\": \"m_probability_description\", \"title\": \"m probability description\", \"type\": \"nominal\"}, {\"field\": \"comparison_name\", \"title\": \"Comparison column name\", \"type\": \"nominal\"}, {\"field\": \"label_for_charts\", \"title\": \"Label\", \"type\": \"ordinal\"}, {\"field\": \"sql_condition\", \"title\": \"SQL condition\", \"type\": \"nominal\"}, {\"field\": \"m_probability\", \"format\": \".4p\", \"title\": \"m probability\", \"type\": \"quantitative\"}, {\"field\": \"u_probability\", \"format\": \".4p\", \"title\": \"u probability\", \"type\": \"quantitative\"}, {\"field\": \"bayes_factor\", \"format\": \",.4f\", \"title\": \"Bayes factor = m/u\", \"type\": \"quantitative\"}, {\"field\": \"log2_bayes_factor\", \"format\": \",.4f\", \"title\": \"Match weight = log2(m/u)\", \"type\": \"quantitative\"}], \"x\": {\"axis\": {\"title\": \"Proportion of record comparisons\"}, \"field\": \"m_probability\", \"type\": \"quantitative\"}, \"y\": {\"axis\": {\"title\": null}, \"field\": \"label_for_charts\", \"sort\": {\"field\": \"comparison_vector_value\", \"order\": \"descending\"}, \"type\": \"nominal\"}}, \"height\": {\"step\": 12}, \"resolve\": {\"scale\": {\"y\": \"independent\"}}, \"title\": {\"text\": \"Amongst matching record comparisons:\", \"fontSize\": 12, \"fontWeight\": \"bold\"}, \"transform\": [{\"filter\": \"(datum.bayes_factor != 'no-op filter due to vega lite issue 4680')\"}], \"width\": 150}, {\"mark\": \"bar\", \"encoding\": {\"color\": {\"value\": \"red\"}, \"row\": {\"field\": \"comparison_name\", \"header\": {\"labels\": false}, \"sort\": {\"field\": \"comparison_sort_order\"}, \"type\": \"nominal\"}, \"tooltip\": [{\"field\": \"u_probability_description\", \"title\": \"u probability description\", \"type\": \"nominal\"}, {\"field\": \"comparison_name\", \"title\": \"Comparison column name\", \"type\": \"nominal\"}, {\"field\": \"label_for_charts\", \"title\": \"Label\", \"type\": \"ordinal\"}, {\"field\": \"sql_condition\", \"title\": \"SQL condition\", \"type\": \"nominal\"}, {\"field\": \"m_probability\", \"format\": \".4p\", \"title\": \"m probability\", \"type\": \"quantitative\"}, {\"field\": \"u_probability\", \"format\": \".4p\", \"title\": \"u probability\", \"type\": \"quantitative\"}, {\"field\": \"bayes_factor\", \"format\": \",.4f\", \"title\": \"Bayes factor = m/u\", \"type\": \"quantitative\"}, {\"field\": \"log2_bayes_factor\", \"format\": \",.4f\", \"title\": \"Match weight = log2(m/u)\", \"type\": \"quantitative\"}], \"x\": {\"axis\": {\"title\": \"Proportion of record comparisons\"}, \"field\": \"u_probability\", \"type\": \"quantitative\"}, \"y\": {\"axis\": {\"title\": null}, \"field\": \"label_for_charts\", \"sort\": {\"field\": \"comparison_vector_value\", \"order\": \"descending\"}, \"type\": \"nominal\"}}, \"height\": {\"step\": 12}, \"resolve\": {\"scale\": {\"y\": \"independent\"}}, \"title\": {\"text\": \"Amongst non-matching record comparisons:\", \"fontSize\": 12, \"fontWeight\": \"bold\"}, \"transform\": [{\"filter\": \"(datum.bayes_factor != 'no-op filter2 due to vega lite issue 4680')\"}], \"width\": 150}], \"data\": {\"name\": \"data-9d9c6a8e8f240fded6a564163503625b\"}, \"title\": {\"text\": \"Proportion of record comparisons in each comparison level by match status\", \"subtitle\": \"(m and u probabilities)\"}, \"$schema\": \"https://vega.github.io/schema/vega-lite/v5.9.3.json\", \"datasets\": {\"data-9d9c6a8e8f240fded6a564163503625b\": [{\"comparison_name\": \"first_name\", \"sql_condition\": \"\\\"first_name_l\\\" = \\\"first_name_r\\\"\", \"label_for_charts\": \"Exact match on first_name\", \"m_probability\": 0.9999, \"u_probability\": 0.7, \"m_probability_description\": \"Amongst matching record comparisons, 99.99% of records are in the exact match on first_name comparison level\", \"u_probability_description\": \"Amongst non-matching record comparisons, 70.00% of records are in the exact match on first_name comparison level\", \"has_tf_adjustments\": false, \"tf_adjustment_column\": null, \"tf_adjustment_weight\": 1.0, \"is_null_level\": false, \"bayes_factor\": 1.4284285714285716, \"log2_bayes_factor\": 0.5144288961117134, \"comparison_vector_value\": 1, \"max_comparison_vector_value\": 1, \"bayes_factor_description\": \"If comparison level is `exact match on first_name` then comparison is 1.43 times more likely to be a match\", \"probability_two_random_records_match\": 0.0001, \"comparison_sort_order\": 0}, {\"comparison_name\": \"first_name\", \"sql_condition\": \"ELSE\", \"label_for_charts\": \"All other comparisons\", \"m_probability\": 0.4600175745072434, \"u_probability\": 0.9942064286024966, \"m_probability_description\": \"Amongst matching record comparisons, 46.00% of records are in the all other comparisons comparison level\", \"u_probability_description\": \"Amongst non-matching record comparisons, 99.42% of records are in the all other comparisons comparison level\", \"has_tf_adjustments\": false, \"tf_adjustment_column\": null, \"tf_adjustment_weight\": 1.0, \"is_null_level\": false, \"bayes_factor\": 0.4626982498532682, \"log2_bayes_factor\": -1.1118564529385215, \"comparison_vector_value\": 0, \"max_comparison_vector_value\": 1, \"bayes_factor_description\": \"If comparison level is `all other comparisons` then comparison is  2.16 times less likely to be a match\", \"probability_two_random_records_match\": 0.0001, \"comparison_sort_order\": 0}, {\"comparison_name\": \"surname\", \"sql_condition\": \"\\\"surname_l\\\" = \\\"surname_r\\\"\", \"label_for_charts\": \"Exact match on surname\", \"m_probability\": 0.5352088162411789, \"u_probability\": 0.004889975550122249, \"m_probability_description\": \"Amongst matching record comparisons, 53.52% of records are in the exact match on surname comparison level\", \"u_probability_description\": \"Amongst non-matching record comparisons, 0.49% of records are in the exact match on surname comparison level\", \"has_tf_adjustments\": false, \"tf_adjustment_column\": null, \"tf_adjustment_weight\": 1.0, \"is_null_level\": false, \"bayes_factor\": 109.45020292132108, \"log2_bayes_factor\": 6.7741308190611695, \"comparison_vector_value\": 1, \"max_comparison_vector_value\": 1, \"bayes_factor_description\": \"If comparison level is `exact match on surname` then comparison is 109.45 times more likely to be a match\", \"probability_two_random_records_match\": 0.0001, \"comparison_sort_order\": 1}, {\"comparison_name\": \"surname\", \"sql_condition\": \"ELSE\", \"label_for_charts\": \"All other comparisons\", \"m_probability\": 0.46479118375882117, \"u_probability\": 0.9951100244498777, \"m_probability_description\": \"Amongst matching record comparisons, 46.48% of records are in the all other comparisons comparison level\", \"u_probability_description\": \"Amongst non-matching record comparisons, 99.51% of records are in the all other comparisons comparison level\", \"has_tf_adjustments\": false, \"tf_adjustment_column\": null, \"tf_adjustment_weight\": 1.0, \"is_null_level\": false, \"bayes_factor\": 0.4670751699197982, \"log2_bayes_factor\": -1.0982733425039641, \"comparison_vector_value\": 0, \"max_comparison_vector_value\": 1, \"bayes_factor_description\": \"If comparison level is `all other comparisons` then comparison is  2.14 times less likely to be a match\", \"probability_two_random_records_match\": 0.0001, \"comparison_sort_order\": 1}, {\"comparison_name\": \"dob\", \"sql_condition\": \"\\\"dob_l\\\" = \\\"dob_r\\\"\", \"label_for_charts\": \"Exact match on dob\", \"m_probability\": 0.95, \"u_probability\": 0.0017477477477477479, \"m_probability_description\": \"Amongst matching record comparisons, 95.00% of records are in the exact match on dob comparison level\", \"u_probability_description\": \"Amongst non-matching record comparisons, 0.17% of records are in the exact match on dob comparison level\", \"has_tf_adjustments\": false, \"tf_adjustment_column\": null, \"tf_adjustment_weight\": 1.0, \"is_null_level\": false, \"bayes_factor\": 543.5567010309278, \"log2_bayes_factor\": 9.086286727381289, \"comparison_vector_value\": 1, \"max_comparison_vector_value\": 1, \"bayes_factor_description\": \"If comparison level is `exact match on dob` then comparison is 543.56 times more likely to be a match\", \"probability_two_random_records_match\": 0.0001, \"comparison_sort_order\": 2}, {\"comparison_name\": \"dob\", \"sql_condition\": \"ELSE\", \"label_for_charts\": \"All other comparisons\", \"m_probability\": 0.050000000000000044, \"u_probability\": 0.9982522522522522, \"m_probability_description\": \"Amongst matching record comparisons, 5.00% of records are in the all other comparisons comparison level\", \"u_probability_description\": \"Amongst non-matching record comparisons, 99.83% of records are in the all other comparisons comparison level\", \"has_tf_adjustments\": false, \"tf_adjustment_column\": null, \"tf_adjustment_weight\": 1.0, \"is_null_level\": false, \"bayes_factor\": 0.05008754038589973, \"log2_bayes_factor\": -4.319404421864071, \"comparison_vector_value\": 0, \"max_comparison_vector_value\": 1, \"bayes_factor_description\": \"If comparison level is `all other comparisons` then comparison is  19.97 times less likely to be a match\", \"probability_two_random_records_match\": 0.0001, \"comparison_sort_order\": 2}, {\"comparison_name\": \"city\", \"sql_condition\": \"\\\"city_l\\\" = \\\"city_r\\\"\", \"label_for_charts\": \"Exact match on city\", \"m_probability\": 0.6694809875123263, \"u_probability\": 0.0551475711801453, \"m_probability_description\": \"Amongst matching record comparisons, 66.95% of records are in the exact match on city comparison level\", \"u_probability_description\": \"Amongst non-matching record comparisons, 5.51% of records are in the exact match on city comparison level\", \"has_tf_adjustments\": false, \"tf_adjustment_column\": null, \"tf_adjustment_weight\": 1.0, \"is_null_level\": false, \"bayes_factor\": 12.139809119161328, \"log2_bayes_factor\": 3.601673832378458, \"comparison_vector_value\": 1, \"max_comparison_vector_value\": 1, \"bayes_factor_description\": \"If comparison level is `exact match on city` then comparison is 12.14 times more likely to be a match\", \"probability_two_random_records_match\": 0.0001, \"comparison_sort_order\": 3}, {\"comparison_name\": \"city\", \"sql_condition\": \"ELSE\", \"label_for_charts\": \"All other comparisons\", \"m_probability\": 0.3305190124876736, \"u_probability\": 0.9448524288198547, \"m_probability_description\": \"Amongst matching record comparisons, 33.05% of records are in the all other comparisons comparison level\", \"u_probability_description\": \"Amongst non-matching record comparisons, 94.49% of records are in the all other comparisons comparison level\", \"has_tf_adjustments\": false, \"tf_adjustment_column\": null, \"tf_adjustment_weight\": 1.0, \"is_null_level\": false, \"bayes_factor\": 0.3498101951227458, \"log2_bayes_factor\": -1.5153557580615085, \"comparison_vector_value\": 0, \"max_comparison_vector_value\": 1, \"bayes_factor_description\": \"If comparison level is `all other comparisons` then comparison is  2.86 times less likely to be a match\", \"probability_two_random_records_match\": 0.0001, \"comparison_sort_order\": 3}]}}, {\"mode\": \"vega-lite\"});\n",
       "</script>"
      ],
      "text/plain": [
       "alt.HConcatChart(...)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import splink.comparison_level_library as cll\n",
    "import splink.comparison_library as cl\n",
    "from splink import DuckDBAPI, Linker, SettingsCreator, block_on, splink_datasets\n",
    "\n",
    "\n",
    "db_api = DuckDBAPI()\n",
    "\n",
    "first_name_comparison = cl.CustomComparison(\n",
    "    comparison_levels=[\n",
    "        cll.NullLevel(\"first_name\"),\n",
    "        cll.ExactMatchLevel(\"first_name\").configure(\n",
    "            m_probability=0.9999,\n",
    "            fix_m_probability=True,\n",
    "            u_probability=0.7,\n",
    "            fix_u_probability=True,\n",
    "        ),\n",
    "        cll.ElseLevel(),\n",
    "    ]\n",
    ")\n",
    "settings = SettingsCreator(\n",
    "    link_type=\"dedupe_only\",\n",
    "    comparisons=[\n",
    "        first_name_comparison,\n",
    "        cl.ExactMatch(\"surname\"),\n",
    "        cl.ExactMatch(\"dob\"),\n",
    "        cl.ExactMatch(\"city\"),\n",
    "    ],\n",
    "    blocking_rules_to_generate_predictions=[\n",
    "        block_on(\"first_name\"),\n",
    "        block_on(\"dob\"),\n",
    "    ],\n",
    "    additional_columns_to_retain=[\"cluster\"],\n",
    ")\n",
    "\n",
    "df = splink_datasets.fake_1000\n",
    "linker = Linker(df, settings, db_api, set_up_basic_logging=False)\n",
    "\n",
    "linker.training.estimate_u_using_random_sampling(max_pairs=1e6)\n",
    "linker.training.estimate_parameters_using_expectation_maximisation(block_on(\"dob\"))\n",
    "\n",
    "linker.visualisations.m_u_parameters_chart()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Manually altering `m` and `u` probabilities post-training\n",
    "\n",
    "This is not officially supported, but can be useful for ad-hoc alterations to trained models."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "<style>\n",
       "  #altair-viz-25439eac84d6494cbc13945e9184ca3b.vega-embed {\n",
       "    width: 100%;\n",
       "    display: flex;\n",
       "  }\n",
       "\n",
       "  #altair-viz-25439eac84d6494cbc13945e9184ca3b.vega-embed details,\n",
       "  #altair-viz-25439eac84d6494cbc13945e9184ca3b.vega-embed details summary {\n",
       "    position: relative;\n",
       "  }\n",
       "</style>\n",
       "<div id=\"altair-viz-25439eac84d6494cbc13945e9184ca3b\"></div>\n",
       "<script type=\"text/javascript\">\n",
       "  var VEGA_DEBUG = (typeof VEGA_DEBUG == \"undefined\") ? {} : VEGA_DEBUG;\n",
       "  (function(spec, embedOpt){\n",
       "    let outputDiv = document.currentScript.previousElementSibling;\n",
       "    if (outputDiv.id !== \"altair-viz-25439eac84d6494cbc13945e9184ca3b\") {\n",
       "      outputDiv = document.getElementById(\"altair-viz-25439eac84d6494cbc13945e9184ca3b\");\n",
       "    }\n",
       "    const paths = {\n",
       "      \"vega\": \"https://cdn.jsdelivr.net/npm/vega@5?noext\",\n",
       "      \"vega-lib\": \"https://cdn.jsdelivr.net/npm/vega-lib?noext\",\n",
       "      \"vega-lite\": \"https://cdn.jsdelivr.net/npm/vega-lite@5.17.0?noext\",\n",
       "      \"vega-embed\": \"https://cdn.jsdelivr.net/npm/vega-embed@6?noext\",\n",
       "    };\n",
       "\n",
       "    function maybeLoadScript(lib, version) {\n",
       "      var key = `${lib.replace(\"-\", \"\")}_version`;\n",
       "      return (VEGA_DEBUG[key] == version) ?\n",
       "        Promise.resolve(paths[lib]) :\n",
       "        new Promise(function(resolve, reject) {\n",
       "          var s = document.createElement('script');\n",
       "          document.getElementsByTagName(\"head\")[0].appendChild(s);\n",
       "          s.async = true;\n",
       "          s.onload = () => {\n",
       "            VEGA_DEBUG[key] = version;\n",
       "            return resolve(paths[lib]);\n",
       "          };\n",
       "          s.onerror = () => reject(`Error loading script: ${paths[lib]}`);\n",
       "          s.src = paths[lib];\n",
       "        });\n",
       "    }\n",
       "\n",
       "    function showError(err) {\n",
       "      outputDiv.innerHTML = `<div class=\"error\" style=\"color:red;\">${err}</div>`;\n",
       "      throw err;\n",
       "    }\n",
       "\n",
       "    function displayChart(vegaEmbed) {\n",
       "      vegaEmbed(outputDiv, spec, embedOpt)\n",
       "        .catch(err => showError(`Javascript Error: ${err.message}<br>This usually means there's a typo in your chart specification. See the javascript console for the full traceback.`));\n",
       "    }\n",
       "\n",
       "    if(typeof define === \"function\" && define.amd) {\n",
       "      requirejs.config({paths});\n",
       "      require([\"vega-embed\"], displayChart, err => showError(`Error loading script: ${err.message}`));\n",
       "    } else {\n",
       "      maybeLoadScript(\"vega\", \"5\")\n",
       "        .then(() => maybeLoadScript(\"vega-lite\", \"5.17.0\"))\n",
       "        .then(() => maybeLoadScript(\"vega-embed\", \"6\"))\n",
       "        .catch(showError)\n",
       "        .then(() => displayChart(vegaEmbed));\n",
       "    }\n",
       "  })({\"config\": {\"view\": {\"continuousWidth\": 300, \"continuousHeight\": 300, \"discreteHeight\": 300, \"discreteWidth\": 400}, \"header\": {\"title\": null}, \"title\": {\"anchor\": \"middle\", \"offset\": 10}}, \"hconcat\": [{\"mark\": \"bar\", \"encoding\": {\"color\": {\"value\": \"green\"}, \"row\": {\"field\": \"comparison_name\", \"header\": {\"labelAlign\": \"left\", \"labelAnchor\": \"middle\", \"labelAngle\": 0}, \"sort\": {\"field\": \"comparison_sort_order\"}, \"type\": \"nominal\"}, \"tooltip\": [{\"field\": \"m_probability_description\", \"title\": \"m probability description\", \"type\": \"nominal\"}, {\"field\": \"comparison_name\", \"title\": \"Comparison column name\", \"type\": \"nominal\"}, {\"field\": \"label_for_charts\", \"title\": \"Label\", \"type\": \"ordinal\"}, {\"field\": \"sql_condition\", \"title\": \"SQL condition\", \"type\": \"nominal\"}, {\"field\": \"m_probability\", \"format\": \".4p\", \"title\": \"m probability\", \"type\": \"quantitative\"}, {\"field\": \"u_probability\", \"format\": \".4p\", \"title\": \"u probability\", \"type\": \"quantitative\"}, {\"field\": \"bayes_factor\", \"format\": \",.4f\", \"title\": \"Bayes factor = m/u\", \"type\": \"quantitative\"}, {\"field\": \"log2_bayes_factor\", \"format\": \",.4f\", \"title\": \"Match weight = log2(m/u)\", \"type\": \"quantitative\"}], \"x\": {\"axis\": {\"title\": \"Proportion of record comparisons\"}, \"field\": \"m_probability\", \"type\": \"quantitative\"}, \"y\": {\"axis\": {\"title\": null}, \"field\": \"label_for_charts\", \"sort\": {\"field\": \"comparison_vector_value\", \"order\": \"descending\"}, \"type\": \"nominal\"}}, \"height\": {\"step\": 12}, \"resolve\": {\"scale\": {\"y\": \"independent\"}}, \"title\": {\"text\": \"Amongst matching record comparisons:\", \"fontSize\": 12, \"fontWeight\": \"bold\"}, \"transform\": [{\"filter\": \"(datum.bayes_factor != 'no-op filter due to vega lite issue 4680')\"}], \"width\": 150}, {\"mark\": \"bar\", \"encoding\": {\"color\": {\"value\": \"red\"}, \"row\": {\"field\": \"comparison_name\", \"header\": {\"labels\": false}, \"sort\": {\"field\": \"comparison_sort_order\"}, \"type\": \"nominal\"}, \"tooltip\": [{\"field\": \"u_probability_description\", \"title\": \"u probability description\", \"type\": \"nominal\"}, {\"field\": \"comparison_name\", \"title\": \"Comparison column name\", \"type\": \"nominal\"}, {\"field\": \"label_for_charts\", \"title\": \"Label\", \"type\": \"ordinal\"}, {\"field\": \"sql_condition\", \"title\": \"SQL condition\", \"type\": \"nominal\"}, {\"field\": \"m_probability\", \"format\": \".4p\", \"title\": \"m probability\", \"type\": \"quantitative\"}, {\"field\": \"u_probability\", \"format\": \".4p\", \"title\": \"u probability\", \"type\": \"quantitative\"}, {\"field\": \"bayes_factor\", \"format\": \",.4f\", \"title\": \"Bayes factor = m/u\", \"type\": \"quantitative\"}, {\"field\": \"log2_bayes_factor\", \"format\": \",.4f\", \"title\": \"Match weight = log2(m/u)\", \"type\": \"quantitative\"}], \"x\": {\"axis\": {\"title\": \"Proportion of record comparisons\"}, \"field\": \"u_probability\", \"type\": \"quantitative\"}, \"y\": {\"axis\": {\"title\": null}, \"field\": \"label_for_charts\", \"sort\": {\"field\": \"comparison_vector_value\", \"order\": \"descending\"}, \"type\": \"nominal\"}}, \"height\": {\"step\": 12}, \"resolve\": {\"scale\": {\"y\": \"independent\"}}, \"title\": {\"text\": \"Amongst non-matching record comparisons:\", \"fontSize\": 12, \"fontWeight\": \"bold\"}, \"transform\": [{\"filter\": \"(datum.bayes_factor != 'no-op filter2 due to vega lite issue 4680')\"}], \"width\": 150}], \"data\": {\"name\": \"data-a5733d8bc2dbab373de54debaa5e8680\"}, \"title\": {\"text\": \"Proportion of record comparisons in each comparison level by match status\", \"subtitle\": \"(m and u probabilities)\"}, \"$schema\": \"https://vega.github.io/schema/vega-lite/v5.9.3.json\", \"datasets\": {\"data-a5733d8bc2dbab373de54debaa5e8680\": [{\"comparison_name\": \"first_name\", \"sql_condition\": \"\\\"first_name_l\\\" = \\\"first_name_r\\\"\", \"label_for_charts\": \"Exact match on first_name\", \"m_probability\": 0.5197676194880654, \"u_probability\": 0.0057935713975033705, \"m_probability_description\": \"Amongst matching record comparisons, 51.98% of records are in the exact match on first_name comparison level\", \"u_probability_description\": \"Amongst non-matching record comparisons, 0.58% of records are in the exact match on first_name comparison level\", \"has_tf_adjustments\": false, \"tf_adjustment_column\": null, \"tf_adjustment_weight\": 1.0, \"is_null_level\": false, \"bayes_factor\": 89.71454459196781, \"log2_bayes_factor\": 6.487269989838904, \"comparison_vector_value\": 1, \"max_comparison_vector_value\": 1, \"bayes_factor_description\": \"If comparison level is `exact match on first_name` then comparison is 89.71 times more likely to be a match\", \"probability_two_random_records_match\": 0.0001, \"comparison_sort_order\": 0}, {\"comparison_name\": \"first_name\", \"sql_condition\": \"ELSE\", \"label_for_charts\": \"All other comparisons\", \"m_probability\": 0.48023238051193456, \"u_probability\": 0.9942064286024966, \"m_probability_description\": \"Amongst matching record comparisons, 48.02% of records are in the all other comparisons comparison level\", \"u_probability_description\": \"Amongst non-matching record comparisons, 99.42% of records are in the all other comparisons comparison level\", \"has_tf_adjustments\": false, \"tf_adjustment_column\": null, \"tf_adjustment_weight\": 1.0, \"is_null_level\": false, \"bayes_factor\": 0.4830308542532478, \"log2_bayes_factor\": -1.0498127487736273, \"comparison_vector_value\": 0, \"max_comparison_vector_value\": 1, \"bayes_factor_description\": \"If comparison level is `all other comparisons` then comparison is  2.07 times less likely to be a match\", \"probability_two_random_records_match\": 0.0001, \"comparison_sort_order\": 0}, {\"comparison_name\": \"surname\", \"sql_condition\": \"\\\"surname_l\\\" = \\\"surname_r\\\"\", \"label_for_charts\": \"Exact match on surname\", \"m_probability\": 0.46081174568408456, \"u_probability\": 0.004889975550122249, \"m_probability_description\": \"Amongst matching record comparisons, 46.08% of records are in the exact match on surname comparison level\", \"u_probability_description\": \"Amongst non-matching record comparisons, 0.49% of records are in the exact match on surname comparison level\", \"has_tf_adjustments\": false, \"tf_adjustment_column\": null, \"tf_adjustment_weight\": 1.0, \"is_null_level\": false, \"bayes_factor\": 94.23600199239529, \"log2_bayes_factor\": 6.558206428324665, \"comparison_vector_value\": 1, \"max_comparison_vector_value\": 1, \"bayes_factor_description\": \"If comparison level is `exact match on surname` then comparison is 94.24 times more likely to be a match\", \"probability_two_random_records_match\": 0.0001, \"comparison_sort_order\": 1}, {\"comparison_name\": \"surname\", \"sql_condition\": \"ELSE\", \"label_for_charts\": \"All other comparisons\", \"m_probability\": 0.1, \"u_probability\": 0.9951100244498777, \"m_probability_description\": \"Amongst matching record comparisons, 10.00% of records are in the all other comparisons comparison level\", \"u_probability_description\": \"Amongst non-matching record comparisons, 99.51% of records are in the all other comparisons comparison level\", \"has_tf_adjustments\": false, \"tf_adjustment_column\": null, \"tf_adjustment_weight\": 1.0, \"is_null_level\": false, \"bayes_factor\": 0.1004914004914005, \"log2_bayes_factor\": -3.3148560462118604, \"comparison_vector_value\": 0, \"max_comparison_vector_value\": 1, \"bayes_factor_description\": \"If comparison level is `all other comparisons` then comparison is  9.95 times less likely to be a match\", \"probability_two_random_records_match\": 0.0001, \"comparison_sort_order\": 1}, {\"comparison_name\": \"dob\", \"sql_condition\": \"\\\"dob_l\\\" = \\\"dob_r\\\"\", \"label_for_charts\": \"Exact match on dob\", \"m_probability\": 0.95, \"u_probability\": 0.0017477477477477479, \"m_probability_description\": \"Amongst matching record comparisons, 95.00% of records are in the exact match on dob comparison level\", \"u_probability_description\": \"Amongst non-matching record comparisons, 0.17% of records are in the exact match on dob comparison level\", \"has_tf_adjustments\": false, \"tf_adjustment_column\": null, \"tf_adjustment_weight\": 1.0, \"is_null_level\": false, \"bayes_factor\": 543.5567010309278, \"log2_bayes_factor\": 9.086286727381289, \"comparison_vector_value\": 1, \"max_comparison_vector_value\": 1, \"bayes_factor_description\": \"If comparison level is `exact match on dob` then comparison is 543.56 times more likely to be a match\", \"probability_two_random_records_match\": 0.0001, \"comparison_sort_order\": 2}, {\"comparison_name\": \"dob\", \"sql_condition\": \"ELSE\", \"label_for_charts\": \"All other comparisons\", \"m_probability\": 0.050000000000000044, \"u_probability\": 0.9982522522522522, \"m_probability_description\": \"Amongst matching record comparisons, 5.00% of records are in the all other comparisons comparison level\", \"u_probability_description\": \"Amongst non-matching record comparisons, 99.83% of records are in the all other comparisons comparison level\", \"has_tf_adjustments\": false, \"tf_adjustment_column\": null, \"tf_adjustment_weight\": 1.0, \"is_null_level\": false, \"bayes_factor\": 0.05008754038589973, \"log2_bayes_factor\": -4.319404421864071, \"comparison_vector_value\": 0, \"max_comparison_vector_value\": 1, \"bayes_factor_description\": \"If comparison level is `all other comparisons` then comparison is  19.97 times less likely to be a match\", \"probability_two_random_records_match\": 0.0001, \"comparison_sort_order\": 2}, {\"comparison_name\": \"city\", \"sql_condition\": \"\\\"city_l\\\" = \\\"city_r\\\"\", \"label_for_charts\": \"Exact match on city\", \"m_probability\": 0.5906525701958633, \"u_probability\": 0.0551475711801453, \"m_probability_description\": \"Amongst matching record comparisons, 59.07% of records are in the exact match on city comparison level\", \"u_probability_description\": \"Amongst non-matching record comparisons, 5.51% of records are in the exact match on city comparison level\", \"has_tf_adjustments\": false, \"tf_adjustment_column\": null, \"tf_adjustment_weight\": 1.0, \"is_null_level\": false, \"bayes_factor\": 10.710400432077687, \"log2_bayes_factor\": 3.4209405143381115, \"comparison_vector_value\": 1, \"max_comparison_vector_value\": 1, \"bayes_factor_description\": \"If comparison level is `exact match on city` then comparison is 10.71 times more likely to be a match\", \"probability_two_random_records_match\": 0.0001, \"comparison_sort_order\": 3}, {\"comparison_name\": \"city\", \"sql_condition\": \"ELSE\", \"label_for_charts\": \"All other comparisons\", \"m_probability\": 0.4093474298041367, \"u_probability\": 0.9448524288198547, \"m_probability_description\": \"Amongst matching record comparisons, 40.93% of records are in the all other comparisons comparison level\", \"u_probability_description\": \"Amongst non-matching record comparisons, 94.49% of records are in the all other comparisons comparison level\", \"has_tf_adjustments\": false, \"tf_adjustment_column\": null, \"tf_adjustment_weight\": 1.0, \"is_null_level\": false, \"bayes_factor\": 0.43323953806778304, \"log2_bayes_factor\": -1.2067631834937573, \"comparison_vector_value\": 0, \"max_comparison_vector_value\": 1, \"bayes_factor_description\": \"If comparison level is `all other comparisons` then comparison is  2.31 times less likely to be a match\", \"probability_two_random_records_match\": 0.0001, \"comparison_sort_order\": 3}]}}, {\"mode\": \"vega-lite\"});\n",
       "</script>"
      ],
      "text/plain": [
       "alt.HConcatChart(...)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import splink.comparison_level_library as cll\n",
    "import splink.comparison_library as cl\n",
    "from splink import DuckDBAPI, Linker, SettingsCreator, block_on, splink_datasets\n",
    "from splink.datasets import splink_dataset_labels\n",
    "\n",
    "labels = splink_dataset_labels.fake_1000_labels\n",
    "\n",
    "db_api = DuckDBAPI()\n",
    "\n",
    "\n",
    "settings = SettingsCreator(\n",
    "    link_type=\"dedupe_only\",\n",
    "    comparisons=[\n",
    "        cl.ExactMatch(\"first_name\"),\n",
    "        cl.ExactMatch(\"surname\"),\n",
    "        cl.ExactMatch(\"dob\"),\n",
    "        cl.ExactMatch(\"city\"),\n",
    "    ],\n",
    "    blocking_rules_to_generate_predictions=[\n",
    "        block_on(\"first_name\"),\n",
    "        block_on(\"dob\"),\n",
    "    ],\n",
    ")\n",
    "df = splink_datasets.fake_1000\n",
    "linker = Linker(df, settings, db_api, set_up_basic_logging=False)\n",
    "\n",
    "linker.training.estimate_u_using_random_sampling(max_pairs=1e6)\n",
    "linker.training.estimate_parameters_using_expectation_maximisation(block_on(\"dob\"))\n",
    "\n",
    "\n",
    "surname_comparison = linker._settings_obj._get_comparison_by_output_column_name(\n",
    "    \"surname\"\n",
    ")\n",
    "else_comparison_level = (\n",
    "    surname_comparison._get_comparison_level_by_comparison_vector_value(0)\n",
    ")\n",
    "else_comparison_level._m_probability = 0.1\n",
    "\n",
    "\n",
    "linker.visualisations.m_u_parameters_chart()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Generate the (beta) labelling tool"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import splink.comparison_library as cl\n",
    "from splink import DuckDBAPI, Linker, SettingsCreator, block_on, splink_datasets\n",
    "\n",
    "db_api = DuckDBAPI()\n",
    "\n",
    "df = splink_datasets.fake_1000\n",
    "\n",
    "settings = SettingsCreator(\n",
    "    link_type=\"dedupe_only\",\n",
    "    comparisons=[\n",
    "        cl.ExactMatch(\"first_name\"),\n",
    "        cl.ExactMatch(\"surname\"),\n",
    "        cl.ExactMatch(\"dob\"),\n",
    "        cl.ExactMatch(\"city\").configure(term_frequency_adjustments=True),\n",
    "        cl.ExactMatch(\"email\"),\n",
    "    ],\n",
    "    blocking_rules_to_generate_predictions=[\n",
    "        block_on(\"first_name\"),\n",
    "        block_on(\"surname\"),\n",
    "    ],\n",
    "    max_iterations=2,\n",
    ")\n",
    "\n",
    "linker = Linker(df, settings, db_api, set_up_basic_logging=False)\n",
    "\n",
    "linker.training.estimate_probability_two_random_records_match(\n",
    "    [block_on(\"first_name\", \"surname\")], recall=0.7\n",
    ")\n",
    "\n",
    "linker.training.estimate_u_using_random_sampling(max_pairs=1e6)\n",
    "\n",
    "linker.training.estimate_parameters_using_expectation_maximisation(block_on(\"dob\"))\n",
    "\n",
    "pairwise_predictions = linker.inference.predict(threshold_match_weight=-10)\n",
    "\n",
    "first_unique_id = df.iloc[0].unique_id\n",
    "linker.evaluation.labelling_tool_for_specific_record(unique_id=first_unique_id, overwrite=True)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Modifying settings after loading from a serialised `.json` model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import splink.comparison_library as cl\n",
    "from splink import DuckDBAPI, Linker, SettingsCreator, block_on, splink_datasets\n",
    "\n",
    "# setup to create a model\n",
    "\n",
    "db_api = DuckDBAPI()\n",
    "\n",
    "df = splink_datasets.fake_1000\n",
    "\n",
    "settings = SettingsCreator(\n",
    "    link_type=\"dedupe_only\",\n",
    "    comparisons=[\n",
    "        cl.LevenshteinAtThresholds(\"first_name\"),\n",
    "        cl.LevenshteinAtThresholds(\"surname\"),\n",
    "\n",
    "    ],\n",
    "    blocking_rules_to_generate_predictions=[\n",
    "        block_on(\"first_name\", \"dob\"),\n",
    "        block_on(\"surname\"),\n",
    "    ]\n",
    ")\n",
    "\n",
    "linker = Linker(df, settings, db_api)\n",
    "\n",
    "\n",
    "linker.misc.save_model_to_json(\"mod.json\", overwrite=True)\n",
    "\n",
    "new_settings = SettingsCreator.from_path_or_dict(\"mod.json\")\n",
    "\n",
    "new_settings.retain_intermediate_calculation_columns = True\n",
    "new_settings.blocking_rules_to_generate_predictions = [\"1=1\"]\n",
    "new_settings.additional_columns_to_retain = [\"cluster\"]\n",
    "\n",
    "linker = Linker(df, new_settings, DuckDBAPI())\n",
    "\n",
    "linker.inference.predict().as_duckdbpyrelation().show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using a DuckDB UDF in a comparison level"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import difflib\n",
    "\n",
    "import duckdb\n",
    "\n",
    "import splink.comparison_level_library as cll\n",
    "import splink.comparison_library as cl\n",
    "from splink import DuckDBAPI, Linker, SettingsCreator, block_on, splink_datasets\n",
    "\n",
    "\n",
    "def custom_partial_ratio(s1, s2):\n",
    "    \"\"\"Custom function to compute partial ratio similarity between two strings.\"\"\"\n",
    "    s1, s2 = str(s1), str(s2)\n",
    "    matcher = difflib.SequenceMatcher(None, s1, s2)\n",
    "    return matcher.ratio()\n",
    "\n",
    "\n",
    "df = splink_datasets.fake_1000\n",
    "\n",
    "con = duckdb.connect()\n",
    "con.create_function(\n",
    "    \"custom_partial_ratio\",\n",
    "    custom_partial_ratio,\n",
    "    [duckdb.typing.VARCHAR, duckdb.typing.VARCHAR],\n",
    "    duckdb.typing.DOUBLE,\n",
    ")\n",
    "db_api = DuckDBAPI(connection=con)\n",
    "\n",
    "\n",
    "fuzzy_email_comparison = {\n",
    "    \"output_column_name\": \"email_fuzzy\",\n",
    "    \"comparison_levels\": [\n",
    "        cll.NullLevel(\"email\"),\n",
    "        cll.ExactMatchLevel(\"email\"),\n",
    "        {\n",
    "            \"sql_condition\": \"custom_partial_ratio(email_l, email_r) > 0.8\",\n",
    "            \"label_for_charts\": \"Fuzzy match (≥ 0.8)\",\n",
    "        },\n",
    "        cll.ElseLevel(),\n",
    "    ],\n",
    "}\n",
    "\n",
    "settings = SettingsCreator(\n",
    "    link_type=\"dedupe_only\",\n",
    "    comparisons=[\n",
    "        cl.ExactMatch(\"first_name\"),\n",
    "        cl.ExactMatch(\"surname\"),\n",
    "        cl.ExactMatch(\"dob\"),\n",
    "        cl.ExactMatch(\"city\").configure(term_frequency_adjustments=True),\n",
    "        fuzzy_email_comparison,\n",
    "    ],\n",
    "    blocking_rules_to_generate_predictions=[\n",
    "        block_on(\"first_name\"),\n",
    "        block_on(\"surname\"),\n",
    "    ],\n",
    "    max_iterations=2,\n",
    ")\n",
    "\n",
    "linker = Linker(df, settings, db_api)\n",
    "\n",
    "linker.training.estimate_probability_two_random_records_match(\n",
    "    [block_on(\"first_name\", \"surname\")], recall=0.7\n",
    ")\n",
    "\n",
    "linker.training.estimate_u_using_random_sampling(max_pairs=1e5)\n",
    "\n",
    "linker.training.estimate_parameters_using_expectation_maximisation(block_on(\"dob\"))\n",
    "\n",
    "pairwise_predictions = linker.inference.predict(threshold_match_weight=-10)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Nested linkage\n",
    "\n",
    "In this example, we want to deduplicate persons but only within each company.\n",
    "\n",
    "The problem is that the companies themselves may be duplicates, so we proceed by deduplicating the companies first and then deduplicating persons nested within each company we resolved in step 1.\n",
    "\n",
    "Note I do not include full model training code here, just a simple/illustrative model spec.  The example is more about demonstrating the nested linkage process."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import duckdb\n",
    "import pandas as pd\n",
    "import os\n",
    "import splink.comparison_library as cl\n",
    "from splink import DuckDBAPI, Linker, SettingsCreator, block_on\n",
    "from splink.clustering import cluster_pairwise_predictions_at_threshold\n",
    "\n",
    "# Example data with companies and persons\n",
    "company_person_records_list = [\n",
    "    {\n",
    "        \"unique_id\": 1001,\n",
    "        \"client_id\": \"GGN1\",\n",
    "        \"company_name\": \"Green Garden Nurseries Ltd\",\n",
    "        \"postcode\": \"NR1 1AB\",\n",
    "        \"person_firstname\": \"John\",\n",
    "        \"person_surname\": \"Smith\",\n",
    "    },\n",
    "    {\n",
    "        \"unique_id\": 1002,\n",
    "        \"client_id\": \"GGN1\",\n",
    "        \"company_name\": \"Green Gardens Ltd\",\n",
    "        \"postcode\": \"NR1 1AB\",\n",
    "        \"person_firstname\": \"Sarah\",\n",
    "        \"person_surname\": \"Jones\",\n",
    "    },\n",
    "    {\n",
    "        \"unique_id\": 1003,\n",
    "        \"client_id\": \"GGN2\",\n",
    "        \"company_name\": \"Green Garden Nurseries Ltd\",\n",
    "        \"postcode\": \"NR1 1AB\",\n",
    "        \"person_firstname\": \"John\",\n",
    "        \"person_surname\": \"Smith\",\n",
    "    },\n",
    "    {\n",
    "        \"unique_id\": 3001,\n",
    "        \"client_id\": \"GW1\",\n",
    "        \"company_name\": \"Garden World\",\n",
    "        \"postcode\": \"LS2 3EF\",\n",
    "        \"person_firstname\": \"Emma\",\n",
    "        \"person_surname\": \"Wilson\",\n",
    "    },\n",
    "    {\n",
    "        \"unique_id\": 3002,\n",
    "        \"client_id\": \"GW1\",\n",
    "        \"company_name\": \"Garden World UK\",\n",
    "        \"postcode\": \"LS2 3EF\",\n",
    "        \"person_firstname\": \"Emma\",\n",
    "        \"person_surname\": \"Wilson\",\n",
    "    },\n",
    "    {\n",
    "        \"unique_id\": 3003,\n",
    "        \"client_id\": \"GW2\",\n",
    "        \"company_name\": \"Garden World\",\n",
    "        \"postcode\": \"LS2 3EF\",\n",
    "        \"person_firstname\": \"Emma\",\n",
    "        \"person_surname\": \"Wilson\",\n",
    "    },\n",
    "    {\n",
    "        \"unique_id\": 3004,\n",
    "        \"client_id\": \"GW2\",\n",
    "        \"company_name\": \"Garden World\",\n",
    "        \"postcode\": \"LS2 3EF\",\n",
    "        \"person_firstname\": \"James\",\n",
    "        \"person_surname\": \"Taylor\",\n",
    "    },\n",
    "]\n",
    "company_person_records = pd.DataFrame(company_person_records_list)\n",
    "company_person_records\n",
    "print(\"========== NESTED COMPANY-PERSON LINKAGE EXAMPLE ==========\")\n",
    "print(\"This example demonstrates a two-phase linkage process:\")\n",
    "print(\"1. First, link and cluster to find duplicate companies (client_id)\")\n",
    "print(\"2. Then, deduplicate persons ONLY within each company cluster\")\n",
    "\n",
    "# Initialize database\n",
    "if os.path.exists(\"nested_linkage.ddb\"):\n",
    "    os.remove(\"nested_linkage.ddb\")\n",
    "con = duckdb.connect(\"nested_linkage.ddb\")\n",
    "\n",
    "# Load data into DuckDB\n",
    "con.execute(\n",
    "    \"CREATE OR REPLACE TABLE company_person_records AS \"\n",
    "    \"SELECT * FROM company_person_records\"\n",
    ")\n",
    "\n",
    "\n",
    "print(\"\\n--- PHASE 1: COMPANY LINKAGE ---\")\n",
    "print(\"Company records to be linked:\")\n",
    "con.table(\"company_person_records\").show()\n",
    "\n",
    "# STEP 1: Find duplicate client_ids\n",
    "\n",
    "\n",
    "# Configure company linkage\n",
    "# We match on person name because if we have duplicate client_ids,\n",
    "# it's likely that they may share the same contact\n",
    "# Note though, at this stage the entity is client not a person\n",
    "company_settings = SettingsCreator(\n",
    "    link_type=\"dedupe_only\",\n",
    "    unique_id_column_name=\"unique_id\",\n",
    "    probability_two_random_records_match=0.001,\n",
    "    comparisons=[\n",
    "        cl.ExactMatch(\"client_id\"),\n",
    "        cl.JaroWinklerAtThresholds(\"person_firstname\"),\n",
    "        cl.JaroWinklerAtThresholds(\"person_surname\"),\n",
    "        cl.JaroWinklerAtThresholds(\"company_name\"),\n",
    "        cl.ExactMatch(\"postcode\"),\n",
    "    ],\n",
    "    blocking_rules_to_generate_predictions=[\n",
    "        block_on(\"postcode\"),\n",
    "        block_on(\"company_name\"),\n",
    "    ],\n",
    "    retain_matching_columns=True,\n",
    ")\n",
    "\n",
    "db_api = DuckDBAPI(connection=con)\n",
    "company_linker = Linker(\"company_person_records\", company_settings, db_api)\n",
    "company_predictions = company_linker.inference.predict(threshold_match_probability=0.5)\n",
    "\n",
    "print(\"\\nCompany pairwise matches:\")\n",
    "company_predictions.as_duckdbpyrelation().show()\n",
    "\n",
    "# Cluster companies\n",
    "company_nodes = con.sql(\"SELECT DISTINCT client_id FROM company_person_records\")\n",
    "company_edges = con.sql(f\"\"\"\n",
    "    SELECT\n",
    "        client_id_l as n_1,\n",
    "        client_id_r as n_2,\n",
    "        match_probability\n",
    "    FROM {company_predictions.physical_name}\n",
    "\"\"\")\n",
    "\n",
    "# Perform company clustering\n",
    "company_clusters = cluster_pairwise_predictions_at_threshold(\n",
    "    company_nodes,\n",
    "    company_edges,\n",
    "    node_id_column_name=\"client_id\",\n",
    "    edge_id_column_name_left=\"n_1\",\n",
    "    edge_id_column_name_right=\"n_2\",\n",
    "    db_api=db_api,\n",
    "    threshold_match_probability=0.5,\n",
    ")\n",
    "\n",
    "# Add company cluster IDs to original records\n",
    "company_clusters_ddb = company_clusters.as_duckdbpyrelation()\n",
    "con.register(\"company_clusters_ddb\", company_clusters_ddb)\n",
    "\n",
    "\n",
    "sql = \"\"\"\n",
    "CREATE TABLE records_with_company_cluster AS\n",
    "SELECT cr.*,\n",
    "       cc.cluster_id as company_cluster_id\n",
    "FROM company_person_records cr\n",
    "LEFT JOIN company_clusters_ddb cc\n",
    "ON cr.client_id = cc.client_id\n",
    "\"\"\"\n",
    "con.execute(sql)\n",
    "print(\"Records with company cluster:\")\n",
    "con.table(\"records_with_company_cluster\").show()\n",
    "\n",
    "# Not needed, just to see what's happening\n",
    "print(\"\\nCompany clustering results:\")\n",
    "con.sql(\"\"\"\n",
    "SELECT\n",
    "    company_cluster_id,\n",
    "    array_agg(DISTINCT client_id) as client_ids,\n",
    "    array_agg(DISTINCT company_name) as company_names\n",
    "FROM records_with_company_cluster\n",
    "GROUP BY company_cluster_id\n",
    "\"\"\").show()\n",
    "\n",
    "print(\"\\n--- PHASE 2: PERSON LINKAGE WITHIN COMPANIES ---\")\n",
    "print(\"Now linking persons, but only within their company clusters\")\n",
    "\n",
    "# STEP 2: Link persons within company clusters\n",
    "# Create a new connection to isolate this step\n",
    "con2 = duckdb.connect()\n",
    "con2.sql(\"attach 'nested_linkage.ddb' as linkage_db\")\n",
    "con2.execute(\n",
    "    \"create table records_with_company_cluster as select * from linkage_db.records_with_company_cluster\"\n",
    ")\n",
    "db_api2 = DuckDBAPI(connection=con2)\n",
    "\n",
    "# Configure person linkage within company clusters\n",
    "# Simple linking model just distinguishes between people within a client_id\n",
    "# There shouldn't be many so this model can be straightforward\n",
    "person_settings = SettingsCreator(\n",
    "    link_type=\"dedupe_only\",\n",
    "    probability_two_random_records_match=0.01,\n",
    "    comparisons=[\n",
    "        cl.JaroWinklerAtThresholds(\"person_firstname\"),\n",
    "        cl.JaroWinklerAtThresholds(\"person_surname\"),\n",
    "    ],\n",
    "    blocking_rules_to_generate_predictions=[\n",
    "        # Critical: Block on company_cluster_id to only compare within company\n",
    "        block_on(\"company_cluster_id\"),\n",
    "    ],\n",
    "    retain_matching_columns=True,\n",
    ")\n",
    "\n",
    "# Link persons within company clusters\n",
    "person_linker = Linker(\"records_with_company_cluster\", person_settings, db_api2)\n",
    "person_predictions = person_linker.inference.predict(threshold_match_probability=0.5)\n",
    "\n",
    "print(\"\\nPerson pairwise matches (within company clusters):\")\n",
    "person_predictions.as_duckdbpyrelation().show(max_width=1000)\n",
    "\n",
    "person_clusters = person_linker.clustering.cluster_pairwise_predictions_at_threshold(\n",
    "    person_predictions, threshold_match_probability=0.5\n",
    ")\n",
    "\n",
    "person_clusters.as_duckdbpyrelation().sort(\"cluster_id\").show(max_width=1000)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Comparing a list of values with fuzzy matching and term frequency adjustments\n",
    "\n",
    "See [here](https://github.com/moj-analytical-services/splink/discussions/2721) for a description of this approach\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import duckdb\n",
    "\n",
    "import splink.comparison_level_library as cll\n",
    "import splink.comparison_library as cl\n",
    "from splink import DuckDBAPI, Linker, SettingsCreator\n",
    "\n",
    "con = duckdb.connect(database=\":memory:\")\n",
    "\n",
    "left_records = [\n",
    "    {\n",
    "        \"unique_id\": 1,\n",
    "        \"primary_forename\": \"Alisha\",\n",
    "        \"all_forenames\": [\"Alisha\", \"Alisha Louise\", \"Ali\"],\n",
    "    },\n",
    "    {\n",
    "        \"unique_id\": 2,\n",
    "        \"primary_forename\": \"Michael\",\n",
    "        \"all_forenames\": [\"Michael\", \"Mike\"],\n",
    "    },\n",
    "]\n",
    "\n",
    "right_records = [\n",
    "    {\"unique_id\": 1, \"primary_forename\": \"Alisha\", \"all_forenames\": [\"Alisha\", \"Ali\"]},\n",
    "    {\"unique_id\": 3, \"primary_forename\": \"Alysha\", \"all_forenames\": [\"Alysha\"]},\n",
    "    {\"unique_id\": 9, \"primary_forename\": \"Michelle\", \"all_forenames\": [\"Michelle\"]},\n",
    "]\n",
    "\n",
    "\n",
    "def make_table(name, recs):\n",
    "    con.execute(f\"drop table if exists {name}\")\n",
    "    con.execute(\n",
    "        f\"\"\"\n",
    "        create table {name} (\n",
    "            unique_id integer,\n",
    "            primary_forename varchar,\n",
    "            all_forenames varchar[]\n",
    "        )\n",
    "    \"\"\"\n",
    "    )\n",
    "    for r in recs:\n",
    "        arr = \"[\" + \", \".join(f\"'{v}'\" for v in r[\"all_forenames\"]) + \"]\"\n",
    "        con.execute(\n",
    "            f\"\"\"\n",
    "            insert into {name} values\n",
    "                ({r[\"unique_id\"]}, '{r[\"primary_forename\"]}', {arr})\n",
    "        \"\"\"\n",
    "        )\n",
    "\n",
    "\n",
    "make_table(\"df_left\", left_records)\n",
    "make_table(\"df_right\", right_records)\n",
    "con.table(\"df_left\").show()\n",
    "con.table(\"df_right\").show()\n",
    "\n",
    "\n",
    "forename_comparison = cl.CustomComparison(\n",
    "    output_column_name=\"forename\",\n",
    "    comparison_levels=[\n",
    "        cll.NullLevel(\"primary_forename\"),\n",
    "        # 1. exact with term-frequency adjustment\n",
    "        cll.ExactMatchLevel(\"primary_forename\", term_frequency_adjustments=True),\n",
    "        # 2. any overlap between arrays\n",
    "        cll.ArrayIntersectLevel(\"all_forenames\", min_intersection=1),\n",
    "        # 3. tight fuzzy\n",
    "        cll.JaroWinklerLevel(\"primary_forename\", distance_threshold=0.9),\n",
    "        # 4. looser fuzzy\n",
    "        cll.JaroWinklerLevel(\"primary_forename\", distance_threshold=0.7),\n",
    "        # 5. fuzzy anywhere in arrays\n",
    "        cll.PairwiseStringDistanceFunctionLevel(\n",
    "            col_name=\"all_forenames\",\n",
    "            distance_function_name=\"jaro_winkler\",\n",
    "            distance_threshold=0.85,\n",
    "        ),\n",
    "        cll.ElseLevel(),\n",
    "    ],\n",
    "    comparison_description=\"Forename comparison combining exact, array overlap and fuzzy logic\",\n",
    ")\n",
    "\n",
    "\n",
    "settings = SettingsCreator(\n",
    "    link_type=\"link_only\",\n",
    "    unique_id_column_name=\"unique_id\",\n",
    "    blocking_rules_to_generate_predictions=[\n",
    "        # create all comparisons for demo\n",
    "        \"1=1\"\n",
    "    ],\n",
    "    comparisons=[forename_comparison],\n",
    "    retain_intermediate_calculation_columns=True,\n",
    "    retain_matching_columns=True,\n",
    ")\n",
    "linker = Linker(\n",
    "    [\"df_left\", \"df_right\"],\n",
    "    settings,\n",
    "    db_api=DuckDBAPI(con),\n",
    ")\n",
    "\n",
    "# Skip training for demo purposes, just demonstrate that predict() works\n",
    "\n",
    "df_predict = linker.inference.predict()\n",
    "df_predict.as_duckdbpyrelation()\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
